const Service = require('egg').Service

class HomeService extends Service {
  async getHomeData() {
    const { ctx, app } = this
    // 获取课程数量
    const courseCount = await ctx.count('Course')
    // 课程栏目
    const columnCount = await ctx.count('CourseCategory')
    // 获取评论总数
    const commentCount = await ctx.sum('Course', 'evaluateCount')
    // 参课人数
    const joinCount = await ctx.sum('CourseTerminfo', 'enrollCount')
    // 计算每年课程
    const courseRes = await ctx.find('CourseTerminfo', {
      attributes: ['startTime'],
      order: [['startTime', 'ASC']]
    }, 'findAll')
    const yearData = {}
    if (courseRes.length > 0) {
      courseRes.forEach(item => {
        const year = new Date(item.dataValues.startTime).getFullYear()
        if (yearData[year]) {
          yearData[year]++
        } else {
          yearData[year] = 1
        }
      })
    }
    // 统计每种栏目拥有的类别数量
    const columnRes = await ctx.find('CourseColumn', {
      attributes: {
        include: [
          [
            app.model.literal('(SELECT COUNT(*) FROM mooc_course_category WHERE column.channel_id = mooc_course_category.channel_id)'),
            'count'
          ]
        ]
      }
    }, 'findAll')
    const columnData = []
    columnRes.forEach(item => {
      const { channelName, count } = item.dataValues
      columnData.push({ channelName, count })
    })
    columnData.sort((a, b) => a.count - b.count)
    const dict = {
      courseCount,
      commentCount,
      joinCount,
      columnCount,
      yearData,
      columnData
    }
    return dict
  }
}

module.exports = HomeService
